﻿using System;
using System.Collections.Generic;
using System.Data;

using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Dapper;
using Oracle.DataAccess.Client;

namespace Capricornus
{
    public abstract class DBCommon<T>
    {

        public IDbConnection GetConn()
        {
            var type = Common.GetConType();
            IDbConnection conn = null;
            switch (type)
            {
                case "oracle":
                    conn = new OracleConnection(Common.GetConstr());
                    break;
                case "sqlserver":
                    conn = new SqlConnection(Common.GetConstr());
                    break;
            }
            return conn;
        }

        public abstract string PrimaryKey { get;  }

        public List<T> GetAll()
        {
            string sql = string.Format("SELECT * FROM {0}", typeof (T).Name);

            var conn = GetConn();

            conn.Open();

            List<T> list = new List<T>();

            if (conn.Query(sql).Any())
            {
                list = conn.Query<T>(sql).ToList();
            }

            conn.Close();

            conn.Dispose();

            return list;
        }

        public int Insert(T t)
        {
            var type = typeof (T);

            string cols = string.Empty;

            var colsValue = string.Empty;

            var conn = GetConn();

            var paraF = GetParaF();

            foreach (var propertyInfo in type.GetProperties())
            {
                cols += propertyInfo.Name + ",";

                colsValue += paraF + propertyInfo.Name + ",";
            }
            cols = cols.EndsWith(",") ? cols.Substring(0, cols.Length - 1) : cols;

            colsValue = colsValue.EndsWith(",") ? colsValue.Substring(0, colsValue.Length - 1) : colsValue;

            string sql = string.Format("INSERT INTO {0}({1}) VALUES({2})", type.Name, cols, colsValue);

            var result = 0;

            conn.Open();

            try
            {
                result = conn.Execute(sql, t);
            }
            catch (Exception e)
            {

                throw e;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public int Update(T t)
        {
            var type = typeof(T);

            string cols = string.Empty;

            var colsValue = string.Empty;

            var conn = GetConn();

            var paraF = GetParaF();

            var upCol = string.Empty;

            foreach (var propertyInfo in type.GetProperties())
            {
                cols = propertyInfo.Name;

                colsValue = paraF + propertyInfo.Name;

                upCol += cols + "=" + colsValue + ",";
            }
            upCol = upCol.EndsWith(",") ? upCol.Substring(0, upCol.Length - 1) : upCol;

            string sql = string.Format("UPDATE {0} SET {1}  WHERE {2}", type.Name, upCol,  PrimaryKey + "=" + paraF + PrimaryKey);

            var result = 0;

            conn.Open();

            try
            {
                result = conn.Execute(sql, t);
            }
            catch (Exception e)
            {

                throw e;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }

        public string GetParaF()
        {
            var type=  Common.GetConType();

            var paraF = string.Empty;

            switch (type)
            {
                case "oracle":

                    paraF = ":";

                    break;

                case "sqlserver":

                    paraF = "@";

                    break;
            }
            return paraF;
        }

        public int Delete(T t)
        {
            string sql = "DELETE FROM {0} WHERE {1}";

            string Keys = PrimaryKey + "=" + GetParaF() + PrimaryKey;

            sql = string.Format(sql, typeof (T).Name, Keys);

            var conn = GetConn();

            conn.Open();

            int result = 0;

            try
            {
                result = conn.Execute(sql, t);
            }
            catch (Exception exception)
            {

                throw exception;
            }
            finally
            {
                conn.Close();
                
                conn.Dispose();
            }
            return result;
        }
    }
}
